const dayjs = require('dayjs')


module.exports = app=>{
    const express = require('express')
    const router = express.Router()

    const assert = require('http-assert')
    const jwt = require('jsonwebtoken')
    const _ = require('lodash')
    const multer = require('multer')

    const {query} = require('../../plugins/db')
    const {compare} = require('../../utils/tool')

    
    /**
     *  用户操作
     */

    router.get('/user',async (req, res)=>{
        const {user_id} = req.query
        let user = await query('SELECT * FROM user WHERE user_id = ?', [user_id])
        res.send(user[0])
    })

    router.put('/update_user', async(req, res)=>{
        const {user_id, user_desc, user_name, user_email, user_pwd} = req.body
        console.log(user_id, user_desc, user_name, user_email);
        let sql = `UPDATE user SET user_email = ?
                    ,user_desc = ?
                    ,user_name = ?,
                    user_pwd = ? 
                    WHERE user_id = ?`;
        let row = await query(sql, [user_email, user_desc, user_name, user_pwd, user_id ])
        assert(!_.isEmpty(row), 402, '修改失败' )
        let user = await query('select * from user where user_id = ?',[user_id])
        res.send(user[0])
    })

    // 申请作者的资料插入审核表
    router.post('/request_author', async (req, res)=>{
        const {user_id, user_desc, user_name, user_email, user_pwd, pen_name} = req.body
        let verify = await query(`SELECT * FROM review_author WHERE user_id = ${user_id} AND review_result = 0`);
        assert(_.isEmpty(verify.length), 402, "处于申请作者中，请勿反复操作！！！")
        let created_at = dayjs(Date.now()).format("YYYY-MM-DD HH:mm:ss")
        let review_result = 0;//审核中
        let sql = `INSERT INTO review_author( user_id, user_name, user_email,pen_name, user_pwd, user_desc, created_at, review_result)
                values(${user_id},'${user_name}','${user_email}','${pen_name}','${user_pwd}','${user_desc}','${created_at}','${review_result}')`
        await query(sql)
        res.send({type:'success', message:"正在申请中"})
    })

    // 用户查看审核后的消息
    router.get('/get_review_news', async (req, res)=>{
        const {user_id} = req.query;
        let sql = `SELECT * FROM review_author WHERE user_id = ${user_id}`;
        let news = await query(sql)
        res.send(news)
    })


    const avator_upload = multer({dest: __dirname + '/../../uploads'});
     router.post('/upload',avator_upload.single('file'), async (req, res)=>{
         console.log(req.headers)
         let file = req.file;
         // 开发模式请求
         if(req.headers.host === 'localhost:3000'){
            file.url = `http://localhost:3000/uploads/${file.filename}`
         }else{
            file.url = `http://121.196.156.175/uploads/${file.filename}`
         }
        
         res.send(file)
     })
    //上传用户头像
    router.put('/update_avator', async (req, res)=>{
        const {user_cover, user_id} = req.body;
        let sql = `UPDATE user SET user_cover = '${user_cover}' WHERE user_id = ${user_id}`;
        await query(sql)
        res.send({
            type: 'success',
            message: "更换头像成功"
        })
    })

    /***小说操作 
    @param  obj.params:[{property, values}]
    @param  obj.orderBy:ASC|DESC
    @param  obj.limit:5    
    **/
    router.get('/novels', async(req, res)=>{
        const {params, orderBy, limit} = req.query
        console.log(params, orderBy, limit);
        let sql = 'SELECT cn.*,ct.type_title FROM cwm_novel cn INNER JOIN cwm_type ct ON cn.type_id = ct.type_id where '
        params.forEach((item, index)=>{
            item = JSON.parse(item)
            if(index === params.length-1){
                sql += `cn.${item.property} = ${item.value} `
            }else{
                sql += `cn.${item.property} = ${item.value} OR `
            }
        })
        sql += ` ORDER BY cn.novel_id ${orderBy}`
        sql =  _.isEmpty(limit)?sql: sql + ` LIMIT ${limit}`
        console.log(sql);
        let novels =  await query(sql)
        
        res.send(novels)
    })

    router.get('/home_type_novels',async (req, res)=>{
        console.log(req.query);
        const {view} = req.query
        let sql = `select * from ${view} where chap_id in (select max(chap_id) 
                from ${view} group by novel_id)  order by ctime DESC  limit 10`
        let novels = await query(sql)
        res.send(novels)
    })

    // DATEDIFF(ctime,now()) > -3 查询3日以内数据
    router.get('/type_novels',async (req, res)=>{
        console.log(req.query);
        const {view, currPage, pageSize, day, other} = req.query
        let sql,total; 
        // 距今天数
        if(day === '不限' && other === '不限'){
            total= await query(`select count(*) as total from ${view} where chap_id in (select max(chap_id) from ${view} 
                group by novel_id) `)
            sql = `select * from ${view} where chap_id in (select max(chap_id) 
                from ${view} group by novel_id) limit ${(currPage-1)*pageSize},${pageSize}`
        }
        // 完结、连载
        else if(day === '不限' && other !== '不限'){
            total= await query(`select count(*) as total from ${view} where chap_id in (select max(chap_id) from ${view} group by novel_id)
                and novel_state='${other}'`)
            sql = `select * from ${view} where chap_id in (select max(chap_id) 
            from ${view} group by novel_id)  and novel_state='${other}' limit ${(currPage-1)*pageSize},${pageSize}`
        }
        else if(day !== '不限' && other === '不限'){
            total= await query(`select count(*) as total from ${view} where chap_id in (select max(chap_id) from ${view} group by novel_id)
                and DATEDIFF(now(),ctime)<${day}`)
            sql = `select * from ${view} where chap_id in (select max(chap_id) 
                from ${view} group by novel_id) and DATEDIFF(now(),ctime)<${day} limit ${(currPage-1)*pageSize},${pageSize}`
        }
        else{
            total= await query(`select count(*) as total from ${view} where chap_id in (select max(chap_id) from ${view} group by novel_id)
                 and DATEDIFF(now(),ctime)< ${day} and novel_state='${other}'`)
            sql = `select * from ${view} where chap_id in (select max(chap_id) 
                from ${view} group by novel_id) and DATEDIFF(now(),ctime)< ${day}
                and novel_state='${other}' limit ${(currPage-1)*pageSize},${pageSize}`
        }
        console.log(sql);
        total = total[0].total;
        let novels = await query(sql)
        let result = {total, novels, currPage, pageSize}
        res.send(result)
    })

    router.get('/novel_detail',async (req, res)=>{
        console.log(req.query);
        const {novel_id} = req.query
        let sql = `
                select 
                cn.*,
                ct.type_title,
                cr.roll_id,
                cr.roll_title,
                cc.chap_title,
                cc.ctime,
                cc.chap_id,
                cco.content_id
                from
                cwm_type ct
                inner join cwm_novel cn  on cn.type_id = ct.type_id
                inner join cwm_roll cr on cr.novel_id = cn.novel_id
                inner join cwm_chapter cc on cr.roll_id = cc.roll_id 
                inner join cwm_content cco on cc.chap_id = cco.chap_id
                where cn.novel_id = ${novel_id}
            `;
            console.log(sql);
        let novels = await query(sql)
        // 生成前端需要数据
        const {novel_title, novel_author, novel_tag, 
            novel_state, cover, novel_intro, type_title} = novels[0]
        let result = {novel_title, novel_author, novel_tag, 
            novel_state, cover, novel_intro, type_title, novel_id}
        let rolls = []
        let rolls_id = []
        novels.forEach(item=>{
            if(!rolls_id.includes(item.roll_id)){
                rolls_id.push(item.roll_id)
                rolls.push({'roll_id': item.roll_id,
                            'roll_title': item.roll_title,
                            'roll_chapters': []})
            }
        })
        rolls.sort(compare('roll_id'))
        for(let roll of rolls){
            for(let item of novels){
                if(item.roll_id === roll.roll_id){
                    roll.roll_chapters.push({
                        'chap_id': item.chap_id,
                        'chap_title': item.chap_title,
                        'ctime': item.ctime
                    })
                }
            }
            // 一遍循环后章节排序
            roll.roll_chapters.sort(compare('chap_id'))
        }
        result.rolls = rolls; 
        result.latest = rolls.slice(-1)[0].roll_chapters.slice(-1)[0]
        console.log(result);
        res.send(result)
    })


 // 获得小说内容
    router.get('/get_novel_content', async (req, res)=>{
        const { chap_id } = req.query
        console.log(chap_id);
        let sql = `select cc.*,cco.content_text,char_length(cco.content_text) as num
                    from cwm_chapter cc inner join  cwm_content cco 
                    on  cc.chap_id = cco.chap_id
                    where cc.chap_id = ${chap_id}
        `
       let content = await query(sql)
       res.send(content[0])
    })
    router.get('/get_novel_banners', async (req, res)=>{
        let sql = `SELECT * FROM banners WHERE table_title = 'cwm_novel' ORDER BY recommend_at DESC LIMIT 4`;
        console.log(sql);
        let banners = await query(sql)
        res.send(banners)
    })

    /***
     *  漫画操作
     */
    router.get('/comic_books', async (req, res)=>{
        let sql = 'SELECT * FROM comic_book';
        let comics = await query(sql)
        res.send(comics)
    })
    
    router.get('/comic_detail', async (req, res)=>{
        const {comic_id} = req.query
        let sql = `
                SELECT
                cb.*,
                cc.comic_chap_id AS chap_id,
                cc.comic_chap_title AS chap_title
            FROM
                comic_book cb
                INNER JOIN comic_chapter cc ON cb.comic_id = cc.comic_id
            WHERE
                cb.comic_id = ${comic_id}`
        let comics = await query(sql)
        const {comic_author, comic_cover, comic_intro, 
            comic_state, comic_title, comic_type} = comics[0]
        let result = {comic_id, comic_author, comic_cover, comic_intro, 
            comic_state, comic_title, comic_type}
        let chapters = [];
        for(let comic of comics){
            chapters.push({
                chap_id: comic.chap_id,
                chap_title: comic.chap_title,
            })
        } 
        for(let i=0; i<chapters.length; i++){
            for(let j=i+1; j<chapters.length; j++){
                if(chapters[i].chap_id === chapters[j].chap_id){
                    chapters.splice(j, 1);
                    j--;
                }
            }
        }
        chapters.sort(compare('chap_id'))
        result.chapters = chapters;
        res.send(result)
    })

    router.get('/get_comic_content', async (req, res)=>{
        const { chap_id, comic_id} = req.query
        let query_chaps_sql = `
            SELECT cc.comic_chap_id  FROM comic_chapter cc  WHERE comic_id = ${comic_id}`
        let chapters_id = await query(query_chaps_sql)
        console.log(chapters_id);
        let sql = `
            SELECT
                cb.comic_id,
                cb.comic_title,
                cc.comic_chap_title,
                cco.* 
            FROM
                comic_book cb
                INNER JOIN comic_chapter cc ON cb.comic_id = cc.comic_id
                INNER JOIN comic_content cco ON cc.comic_chap_id = cco.comic_chap_id 
            WHERE
                cc.comic_chap_id = ${chap_id}`
        let contents = await query(sql)
        const {comic_title, comic_chap_title} = contents[0]
        let result = {
            chap_id,
            comic_title,
            comic_id,
            comic_chap_title
        }
        result.contents = contents
        result.chapters_id = chapters_id
        res.send(result)
    })
    router.get('/get_comic_banners', async (req, res)=>{
        let sql = `SELECT b.*,cb.comic_title FROM banners b INNER JOIN comic_book cb ON b.work_id = cb.comic_id
         WHERE table_title = 'comic_book' ORDER BY recommend_at DESC LIMIT 4`;
         console.log(sql);
         let banners = await query(sql)
        
        res.send(banners)
    })

/***
 *  电子书管理
 * 
 */
    router.get('/type_ebooks', async (req, res)=>{
        const {type_title, page} = req.query;
        console.log(type_title, page);
        let sql;
        if(type_title === '全部'){
            sql =  `SELECT e.*,et.type_title FROM ebook e
            INNER JOIN ebook_type  et ON  e.type_id = et.type_id order by ebook_ctime desc `;
        }else{
            sql = `SELECT e.*,et.type_title FROM ebook e
             INNER JOIN ebook_type  et ON  e.type_id = et.type_id 
             WHERE et.type_title = '${type_title}'`
        }
        let ebooks = await query(sql)
        let total = ebooks.length;
        ebooks =  ebooks.filter((item,index)=>{
            if(index>=(page-1)*12 && index< page*12){
                return true;
            }else return false;
        })  
        res.send({ebooks, total})

    })

   

    router.get('/get_ebook', async (req, res)=>{
        const {type_title, ebook_id} = req.query;
        console.log(type_title, ebook_id);
        let sql ;
        if(type_title === '全部'){
            sql = `SELECT e.*,et.type_title FROM ebook e
                INNER JOIN ebook_type  et ON  e.type_id = et.type_id 
                WHERE e.ebook_id = ${ebook_id}`;
        }
        else{
            sql = `SELECT e.*,et.type_title FROM ebook e
                INNER JOIN ebook_type  et ON  e.type_id = et.type_id 
                WHERE et.type_title = '${type_title}'
                AND e.ebook_id = ${ebook_id}`;
        }
         
            console.log(sql);
        let ebook = await query(sql)
        // console.log(ebook);
        res.send(ebook[0])
    })

    router.post('/search_ebooks', async (req, res)=>{
        const {type_title, val} = req.body;
        console.log(type_title, val);
        let sql;
        if(type_title === '全部'){
            sql =  `SELECT * FROM ebook e INNER JOIN 
            ebook_type  et ON  e.type_id = et.type_id
            WHERE e.ebook_title LIKE '%${val}%' OR
            e.ebook_author LIKE '%${val}%'`
        }else{
            sql = `SELECT * FROM ebook e INNER JOIN 
            ebook_type  et ON  e.type_id = et.type_id
            WHERE et.type_title = '${type_title}'
            AND (e.ebook_title LIKE '%${val}%' OR
            e.ebook_author LIKE '%${val}%')`;
        }
        console.log(sql);
        let ebooks = await  query(sql)
        let total = ebooks.length
        res.send({ebooks, total})
    })

    router.get('/latest_ebooks', async (req, res)=>{
        let sql = `SELECT e.*,et.type_title FROM  ebook e INNER JOIN 
        ebook_type  et ON e.type_id = et.type_id order by ebook_ctime desc limit 8`
        let ebooks = await query(sql)
        res.send(ebooks)
    })

    // 电子书标签生成
    router.get('/ebook_tags', async (req, res)=>{
        let sql = `SELECT ebook_tag FROM ebook`
        let tags = await query(sql)
        let tags_arr =  []
        tags.forEach(item=>{
            tags_arr = [...tags_arr,...item.ebook_tag.split(',')]
        })
        tags_result =  Array.from(new Set(tags_arr))
        console.log(tags_result);
        res.send(tags_result)
    })

    // 查询同一标签下的所有电子书
    router.get('/ebook_tag_list', async(req, res)=>{
        const {tag} = req.query;
        let sql = `SELECT e.*,et.type_title FROM  ebook e INNER JOIN 
                ebook_type  et ON e.type_id = et.type_id
                WHERE ebook_tag LIKE '%${tag}%' `
        let ebooks = await query(sql)
        res.send(ebooks)
    })


    /**
     *  书架管理
     */
    router.put('/add_bookshelf', async (req, res)=>{
        const {table_title, user_id, book_id} = req.body;
        console.log(table_title, user_id, book_id);
        let verify_sql = `SELECT * FROM bookshelf WHERE user_id = ${user_id}`
        let row = await query(verify_sql)
        console.log(row);
        if(_.isEmpty(row)){
            let insert_bshelf_sql = `INSERT INTO bookshelf(user_id) values(${user_id})`;
            await query(insert_bshelf_sql)
        }
        let bshelf_id= await query(`SELECT bshelf_id FROM bookshelf WHERE user_id = ${user_id}`)
        console.log("id---",bshelf_id);
        // 判断关联表是否有数据了
        let is_data_sql = `SELECT * FROM book_bshelf WHERE bshelf_id =${bshelf_id[0].bshelf_id} AND work_id = ${book_id}`
        let is_data_row =await  query(is_data_sql)
        if(!_.isEmpty(is_data_row)){
            res.send({type:'error', message:"添加书架失败"})
            return ;
        }
        let insert_bbshelf_sql = `INSERT INTO book_bshelf(bshelf_id, work_id, table_title) 
                    values(${bshelf_id[0].bshelf_id}, ${book_id}, '${table_title}')`
        console.log(insert_bbshelf_sql);
        let rows = await query(insert_bbshelf_sql)
        if(rows){
            res.send({type:'success',message:"添加书架成功"})
        }else res.send({type:'error', message:"添加书架失败"})
    })

    // 显示个人书架
    router.post('/show_mybookshlef', async (req, res)=>{
        const {user_id,table_title} = req.body;
        console.log(user_id, table_title)
        let sql = `
            SELECT *  FROM book_bshelf 
            WHERE
                bshelf_id IN ( SELECT bshelf_id FROM bookshelf WHERE user_id = ${user_id} ) 
                AND table_title = '${table_title}'`
        let works_id = await query(sql)
        if(!works_id.length){
            res.send([])
            return;
        }
        works_id = works_id.map(item=>{
            return item.work_id
        })
        console.log(works_id);
        let find_works_sql;
        if(table_title === 'cwm_novel'){
            find_works_sql = `SELECT * FROM ${table_title} WHERE novel_id in (${works_id})`
        }else if(table_title === 'comic_book'){
            find_works_sql = `SELECT * FROM ${table_title} WHERE comic_id in (${works_id})`
        }
        let works = await query(find_works_sql)
        res.send(works)
    } )

    // 删除个人书架上的书籍
    router.delete('/delete_shelf_works', async(req, res)=>{
        const {user_id, work_id} = req.query;
        console.log(user_id, work_id);
        let bshelfIds= await query(`SELECT bb.bb_id FROM book_bshelf bb natural join bookshelf b 
            WHERE b.user_id = ${user_id} AND bb.work_id = ${work_id}`)
        bshelfIds= bshelfIds[0].bb_id;
        console.log(bshelfIds);
        await query(`DELETE FROM book_bshelf WHERE bb_id = ${bshelfIds}`)
        res.send({type:'success', message:"删除成功"})

    })

    // 显示书架的数量
    router.get('/get_shelf_sum', async(req, res)=>{
        const {user_id } = req.query;
        let sql = `SELECT count(*) as sum FROM book_bshelf 
            WHERE bshelf_id IN ( SELECT bshelf_id  FROM bookshelf  WHERE user_id = ${user_id})`;
         let result = await query(sql)
         console.log(result);
         res.send(result[0])
    })
    // 显示消息数量
    router.get('/get_notices', async(req, res)=>{
        const {user_id} =  req.query;
        let sql = `SELECT count(*) as notice FROM review_author WHERE user_id = ${user_id} 
            AND feedback != '' ORDER BY review_at DESC`;
        console.log(sql);
        let notices = await query(sql)
        res.send(notices[0])
    })

     // 子路由请求，添加验证权限中间键
     app.use('/web/api',router)

    /**
     * 
     *  全局登录和注册
     * 
     */
     app.post('/web/api/register', async (req, res)=>{
        console.log(req.body);
        const {user_account, user_name, user_pwd} = req.body;
        sql = 'select * from user where user_account = ?';
        user = await query(sql, [user_account])
        assert(_.isEmpty(user[0]), 422, '注册失败，该账户已经存在')
        row = await query('insert into user(user_account, user_pwd, user_name) values(?,?,?)',[user_account, user_pwd, user_name])
        res.status(200).send({message:'注册成功',type:'success'});
        res.send(req.body)
     })
     app.post('/web/api/login', async (req, res)=>{
        console.log(req.body);
        const { user_account, user_pwd } = req.body
        
        // 1. 根据用户名查找密码
        const user = await query('select * from user where user_account =?',[user_account])
        console.log(user);
        assert(!_.isEmpty(user), 422, '用户不存在')
        // 2. 检验密码
        assert(user_pwd === user[0].user_pwd, 422, '密码错误')
        // 3. 返回token
        const token = jwt.sign({user_account:user_account}, app.get('secret'))
        res.send({user:user[0], token:token, id:user[0].user_id})
     })

     // 错误处理函数
    app.use(async (err, req, res, next) => {
        console.log(err.statusCode)
        res.status(err.statusCode || 500).send({
            message: err.message
        })
    })

}